package com.moioo.exam;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Example;

import com.moioo.util.PageAuger;

/**
 * A data access object (DAO) providing persistence and search support for TUser
 * entities. Transaction control of the save(), update() and delete() operations
 * can directly support Spring container-managed transactions or they can be
 * augmented to handle user-managed Spring transactions. Each of these methods
 * provides additional information for how to configure it for the desired type
 * of transaction control.
 * 
 * @see com.moioo.exam.TUser
 * @author MyEclipse Persistence Tools
 */

public class TUserLogDAO extends BaseHibernateDAO {
	private static final Log log = LogFactory.getLog(TUserLogDAO.class);
	// property constants
	public static final String USERNAME = "username";
	public static final String ORGANNAME = "organname";

	public void save(TUserLog transientInstance) {
		log.debug("saving TUserLog instance");
		try {
			getSession().save(transientInstance);
			log.debug("save successful");
		} catch (RuntimeException re) {
			log.error("save failed", re);
			throw re;
		}
	}

	public void delete(TUserLog persistentInstance) {
		log.debug("deleting TUserLog instance");
		try {
			getSession().delete(persistentInstance);
			log.debug("delete successful");
		} catch (RuntimeException re) {
			log.error("delete failed", re);
			throw re;
		}
	}

	public TUserLog findById(java.lang.Integer id) {
		log.debug("getting TUserLog instance with id: " + id);
		try {
			TUserLog instance = (TUserLog) getSession().get("com.moioo.exam.TUserLog",
					id);
			return instance;
		} catch (RuntimeException re) {
			log.error("get failed", re);
			throw re;
		}
	}

	public List findByExample(TUserLog instance) {
		log.debug("finding TUserLog instance by example");
		try {
			List results = getSession().createCriteria("com.moioo.exam.TUserLog")
					.add(Example.create(instance)).list();
			log.debug("find by example successful, result size: "
					+ results.size());
			return results;
		} catch (RuntimeException re) {
			log.error("find by example failed", re);
			throw re;
		}
	}

	public List findByProperty(String propertyName, Object value) {
		log.debug("finding TUserLog instance with property: " + propertyName
				+ ", value: " + value);
		try {
			String queryString = "from TUserLog as model where model."
					+ propertyName + "= ?";
			Query queryObject = getSession().createQuery(queryString);
			queryObject.setParameter(0, value);
			return queryObject.list();
		} catch (RuntimeException re) {
			log.error("find by property name failed", re);
			throw re;
		}
	}

	public List<TUserLog> findAll(PageAuger pa) {
		log.debug("finding all TUserLog instances");
		try {
			String whereString = "";
			String queryString = "from T_UserLog t ";
			if(pa.getStringWhereAuger("inputDateB",null) != null&&!pa.getStringWhereAuger("inputDateB",null).equals(""))
				whereString += " AND t.starttime >= '" + pa.getStringWhereAuger("inputDateB","1900-01-01")+"'";
			if(pa.getStringWhereAuger("inputDateE",null) != null&&!pa.getStringWhereAuger("inputDateE",null).equals(""))
				whereString += " AND t.starttime <= '" + pa.getStringWhereAuger("inputDateE","2090-01-01")+"'";
			if(pa.getStringWhereAuger("organname","").trim().length()>0)
				whereString += " AND t.organname like '%"+pa.getStringWhereAuger("organname","0")+",%'))";
			if(whereString.indexOf("AND") != -1)
				whereString = " WHERE " + whereString.substring(4);
			queryString += whereString ;
			Query  rows = getSession().createQuery("select count(distinct t.userid) from TUserLog t "+whereString);
			pa.setRowCount(rows.uniqueResult());
			queryString = "select top "+pa.getPageIndex()*pa.getPageSize()+" organname,username,max(organid) organid,count(*) as count,sum(datediff(ss,t.starttime,t.endtime)) as time " +queryString+" group by organname,username";
			Connection con=getSession().connection();
			ResultSet rs=null;
			Statement Rs=null;
			List<TUserLog> list=new java.util.ArrayList();
			try
			{
				Rs=con.createStatement();
				System.out.println(queryString);
				rs=Rs.executeQuery(queryString);
				int i=0;
				while (rs.next())
				{
					if (i<(pa.getPageIndex()-1)*pa.getPageSize())
					{
						i++;
						continue;
					}
					TUserLog log=new TUserLog();
					log.setOrganname(rs.getString("organname"));
					log.setUsername(rs.getString("username"));
					log.setOrganid(rs.getInt("organid"));
					log.setCount(rs.getInt("count"));
					log.setTime(rs.getLong("time"));
					list.add(log);
				}
			}catch(Exception e)
			{
				e.printStackTrace();
			}finally
			{
				try
				{
					if (rs!=null)rs.close();
					rs=null;
					if (Rs!=null)Rs.close();
					Rs=null;
					if (con!=null)con.close();
					con=null;
				}catch(Exception e)
				{}
			}
			return list;
		} catch (RuntimeException re) {
			log.error("find all failed", re);
			throw re;
		}
	}
	public TUserLog merge(TUserLog detachedInstance) {
		log.debug("merging TUserLog instance");
		try {
			TUserLog result = (TUserLog) getSession().merge(detachedInstance);
			log.debug("merge successful");
			return result;
		} catch (RuntimeException re) {
			log.error("merge failed", re);
			throw re;
		}
	}

	public void attachDirty(TUserLog instance) {
		log.debug("attaching dirty TUserLog instance");
		try {
			getSession().saveOrUpdate(instance);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}

	public void attachClean(TUserLog instance) {
		log.debug("attaching clean TUserLog instance");
		try {
			getSession().lock(instance, LockMode.NONE);
			log.debug("attach successful");
		} catch (RuntimeException re) {
			log.error("attach failed", re);
			throw re;
		}
	}
}